package com.ctcemti.mdm.project.tool.generatecode.service;

import com.alibaba.fastjson.JSONObject;
import com.ctcemti.mdm.common.exception.UnCanConnectException;
import com.ctcemti.mdm.common.util.JdbcUtil;
import com.ctcemti.mdm.project.tool.generatecode.dao.GenTableColumnMapper;
import com.ctcemti.mdm.project.tool.generatecode.dao.GenTableMapper;
import com.ctcemti.mdm.project.tool.generatecode.entity.GenTable;
import com.ctcemti.mdm.project.tool.generatecode.entity.GenTableColumn;
import com.ctcemti.mdm.project.tool.generatecode.entity.JDBCProperies;
import com.ctcemti.mdm.project.tool.generatecode.util.*;
import org.apache.commons.io.IOUtils;
import org.apache.velocity.Template;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.Velocity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * 业务 服务层实现
 * 
 * @author bxf
 */
@Service
public class GenTableServiceImpl implements IGenTableService
{
    private static final Logger log = LoggerFactory.getLogger(GenTableServiceImpl.class);

    @Autowired
    private GenTableMapper genTableMapper;

    @Autowired
    private GenTableColumnMapper genTableColumnMapper;

    @Autowired
    private IGenTableColumnService genTableColumnService;

    /**
     * 查询业务信息
     *
     * @param id 业务ID
     * @return 业务信息
     */
    @Override
    public GenTable selectGenTableById(Long id)
    {
        GenTable genTable = genTableMapper.selectGenTableById(id);
        setTableFromOptions(genTable);
        return genTable;
    }

    /**
     * 查询业务列表
     *
     * @param genTable 业务信息
     * @return 业务集合
     */
    @Override
    public List<GenTable> selectGenTableList(GenTable genTable)
    {
        return genTableMapper.selectGenTableList(genTable);
    }
    /**
     * 查询业务列表
     *
     * @param genTable 业务信息
     * @return 业务集合
     */
    @Override
    public List<GenTable> selectGenTableListForJDBC(GenTable genTable)
    {
        return genTableMapper.selectGenTableListForJDBC(genTable);
    }

    /**
     * 查询据库列表
     *
     * @param genTable 业务信息
     * @return 数据库表集合
     */
    public List<GenTable> selectDbTableList(GenTable genTable)
    {
        return genTableMapper.selectDbTableList(genTable);
    }

    /**
     * 查询据库列表
     *
     * @param genTable
     * @param jdbcProperies
     * @return 数据库表集合
     */
    @Override
    public List<GenTable> selectDbTableListByJDBC(GenTable genTable, JDBCProperies jdbcProperies) throws UnCanConnectException, SQLException {
        String sql = "";
        if(jdbcProperies.getDatasourceType()==1){//mysql
            StringBuffer sb = new StringBuffer();
            sb.append("select table_name as tableName, table_comment as tableComment, create_time as createTime, update_time as updateTime from information_schema.tables " +
                        " where table_schema = (select database()) " +
                        " AND table_name NOT LIKE 'gen_%' ");
            if(!com.ctcemti.mdm.common.util.StringUtils.isNullOrEmpty(genTable.getTableName())){
                sb.append(" AND table_name like concat('%', ?, '%') ");
            }
            if(!com.ctcemti.mdm.common.util.StringUtils.isNullOrEmpty(genTable.getTableComment())){
                sb.append(" AND table_comment like concat('%', ?, '%') ");
            }
            sql = sb.toString();
            jdbcProperies.setDriver("com.mysql.cj.jdbc.Driver");
        }else if(jdbcProperies.getDatasourceType()==2){//oracle
            StringBuffer sb = new StringBuffer();
            sb.append("select lower(dt.table_name) as \"tableName\", dtc.comments as \"tableComment\", uo.created as \"createTime\", uo.last_ddl_time as \"updateTime\" " +
                        " from user_tables dt, user_tab_comments dtc, user_objects uo " +
                        " where dt.table_name = dtc.table_name " +
                        " and dt.table_name = uo.object_name " +
                        " and uo.object_type = 'TABLE' " +
                        " AND dt.table_name NOT LIKE 'GEN_%' ");
            if(!com.ctcemti.mdm.common.util.StringUtils.isNullOrEmpty(genTable.getTableName())){
                sb.append(" AND lower(dt.table_name)  like concat(concat('%', ?), '%') ");
            }
            if(!com.ctcemti.mdm.common.util.StringUtils.isNullOrEmpty(genTable.getTableComment())){
                sb.append(" AND lower(dtc.comments) like concat(concat('%', ?), '%') ");
            }
            sql = sb.toString();
            jdbcProperies.setDriver("oracle.jdbc.driver.OracleDriver");
        }else{//sqlserver
            StringBuffer sb = new StringBuffer();
            sb.append("select  obj.name tableName ,convert ( varchar (100), ds.value ) as tableComment,obj.create_date as createTime,obj.modify_date as updateTime " +
                    " from sys.objects obj left join (select * from sys.extended_properties where minor_id = 0) ds on ds.major_id = obj.object_id" +
                    " where obj.name not like 'gen_%' and obj.type='u' ");
//            sb.append(" and obj.name not in ( " +
//                        " select" +
//                        " table_name collate chinese_prc_ci_as" +
//                        " from" +
//                        " gen_table) ");
            if(!com.ctcemti.mdm.common.util.StringUtils.isNullOrEmpty(genTable.getTableName())){
                sb.append(" and obj.name = ? ");
            }
            if(!com.ctcemti.mdm.common.util.StringUtils.isNullOrEmpty(genTable.getTableComment())){
                sb.append(" AND ds.value = ? ");
            }

            sql = sb.toString();
            jdbcProperies.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }
        Connection connect = null;
        List<GenTable> gentableList = null;
        List<GenTable> newgentableList = new ArrayList<GenTable>();
        try {
            connect = JdbcUtil.getConnect(jdbcProperies);
            List<GenTable> list = genTableMapper.selectTableByJdbcUrl(jdbcProperies.getJdbcurl());
            List<String> gentablename =  new ArrayList<String>();
            for(GenTable genTable1:list){
                gentablename.add(genTable1.getTableName());
            }
            gentableList = JdbcUtil.selectTableData(connect, sql, genTable);
            for(int i=0;i<gentableList.size();i++){
                if(!gentablename.contains(gentableList.get(i).getTableName())){
                    newgentableList.add(gentableList.get(i));
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new UnCanConnectException("无法连接数据库！");
        }
        return newgentableList;
    }

    /**
     * 查询据库列表
     *
     * @param tableNames 表名称组
     * @return 数据库表集合
     */
    public List<GenTable> selectDbTableListByNames(String[] tableNames)
    {
        return genTableMapper.selectDbTableListByNames(tableNames);
    }

    /**
     * 查询据库列表
     *
     * @param tableNames 表名称组
     * @return 数据库表集合
     */
    @Override
    public List<GenTable> selectDbTableListByNamesForJDBC(String[] tableNames, JDBCProperies jdbcProperies) throws UnCanConnectException {
        String sql = "";
        if(jdbcProperies.getDatasourceType()==1){//mysql
            StringBuffer sb = new StringBuffer();
            sb.append("select table_name as tableName, table_comment as tableComment, create_time as createTime, update_time as updateTime from information_schema.tables " +
                    " where table_name NOT LIKE 'qrtz_%' and table_name NOT LIKE 'gen_%' and table_schema = (select database()) " +
                    " and table_name in ( ");
            String tableNamesForPP = "";
            for(int i=0;i<tableNames.length;i++){
                if(i==tableNames.length-1){
                    tableNamesForPP += "?";
                }else{
                    tableNamesForPP += "?,";
                }

            }
            sb.append(tableNamesForPP+" )");
            sql = sb.toString();
            jdbcProperies.setDriver("com.mysql.cj.jdbc.Driver");
        }else if(jdbcProperies.getDatasourceType()==2){//oracle
            StringBuffer sb = new StringBuffer();
            sb.append("select lower(dt.table_name) as \"tableName\", dtc.comments as \"tableComment\", uo.created as \"createTime\", uo.last_ddl_time as \"updateTime\" " +
                    " from user_tables dt, user_tab_comments dtc, user_objects uo " +
                    " where dt.table_name = dtc.table_name " +
                    " and dt.table_name = uo.object_name " +
                    " and uo.object_type = 'TABLE' " +
                    " AND dt.table_name NOT LIKE 'QRTZ_%' AND dt.table_name NOT LIKE 'GEN_%' " +
                    //" AND dt.table_name NOT IN (select table_name from gen_table) " +
                    " and lower(dt.table_name) in ( ");
            String tableNamesForPP = "";
            for(int i=0;i<tableNames.length;i++){
                if(i==tableNames.length-1){
                    tableNamesForPP += "?";
                }else{
                    tableNamesForPP += "?,";
                }

            }
            sb.append(tableNamesForPP+" )");
            sql = sb.toString();
            jdbcProperies.setDriver("oracle.jdbc.driver.OracleDriver");
        }else{//sqlserver
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT  obj.name tableName , " +
                        " CONVERT (VARCHAR(100), ds.value) as tableComment, " +
                        " obj.create_date AS createTime, " +
                        " obj.modify_date AS updateTime " +
                        " FROM    sys.objects obj " +
                        " LEFT JOIN (select * from sys.extended_properties where minor_id = 0) ds ON ds.major_id = obj.object_id " +
                        " WHERE " +
                        " obj.name NOT LIKE 'gen_%'  and obj.type='U' " +
                        //" AND obj.name NOT IN (select table_name collate Chinese_PRC_CI_AS from gen_table) "+
                        " and obj.name in ( ");
            String tableNamesForPP = "";
            for(int i=0;i<tableNames.length;i++){
                if(i==tableNames.length-1){
                    tableNamesForPP += "?";
                }else{
                    tableNamesForPP += "?,";
                }
            }
            sb.append(tableNamesForPP+" )");
            sql = sb.toString();
            jdbcProperies.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }
        Connection connect = null;
        List<GenTable> list = null;
        List<GenTable> newgentableList = new ArrayList<GenTable>();
        try {
            connect = JdbcUtil.getConnect(jdbcProperies);
            List<GenTable> list1 = genTableMapper.selectTableByJdbcUrl(jdbcProperies.getJdbcurl());
            List<String> gentablename =  new ArrayList<String>();
            for(GenTable genTable1:list1){
                gentablename.add(genTable1.getTableName());
            }
            list = JdbcUtil.selectTablesByNames(connect,sql,tableNames);
            for(int i=0;i<list.size();i++){
                if(!gentablename.contains(list.get(i).getTableName())){
                    newgentableList.add(list.get(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new UnCanConnectException("无法连接数据库！");
        }
        return newgentableList;
    }

    /**
     * 查询据库列表
     *
     * @param tableNames 表名称组
     * @return 数据库表集合
     */
    public List<GenTable> selectDbTableListByNamesForJDBC(String[] tableNames)
    {
        return genTableMapper.selectDbTableListByNames(tableNames);
    }

    /**
     * 修改业务
     *
     * @param genTable 业务信息
     * @return 结果
     */
    @Override
    @Transactional
    public void updateGenTable(GenTable genTable)
    {
        if(!StringUtils.isEmpty(genTable.getTreeCode())){
//            String options = JSON.toJSONString(genTable.getParams());
            String options = "{\"treeName\":\""+genTable.getTreeName()+"\",\"treeParentCode\":\""+genTable.getTreeParentCode()+"\",\"treeCode\":\""+genTable.getTreeCode()+"\"}";
            genTable.setOptions(options);
        }
        int row = genTableMapper.updateGenTable(genTable);
        if(genTable.getColumns()!=null && genTable.getColumns().size()>0){
            for (GenTableColumn cenTableColumn : genTable.getColumns())
            {
                genTableColumnMapper.updateGenTableColumn(cenTableColumn);
            }
        }
    }

    /**
     * 删除业务对象
     *
     * @param ids 需要删除的数据ID
     * @return 结果
     */
    @Override
    @Transactional
    public void deleteGenTableByIds(String ids)
    {
        genTableMapper.deleteGenTableByIds(Convert.toLongArray(ids));
        genTableColumnMapper.deleteGenTableColumnByIds(Convert.toLongArray(ids));
    }

    /**
     * 导入表结构
     *
     * @param tableList 导入表列表
     */
    @Override
    @Transactional
    public void importGenTable(List<GenTable> tableList)
    {
        String operName = "";
        for (GenTable table : tableList)
        {
            try
            {
                String tableName = table.getTableName();
                GenUtils.initTable(table, operName);
                int row = genTableMapper.insertGenTable(table);
                if (row > 0)
                {
                    // 保存列信息
                    List<GenTableColumn> genTableColumns = genTableColumnMapper.selectDbTableColumnsByName(tableName);
                    for (GenTableColumn column : genTableColumns)
                    {
                        GenUtils.initColumnField(column, table);
                        genTableColumnMapper.insertGenTableColumn(column);
                    }
                }
            }
            catch (Exception e)
            {
                log.error("表名 " + table.getTableName() + " 导入失败：", e);
            }
        }
    }
    /**
     * 导入表结构
     *
     * @param tableList 导入表列表
     */
    @Override
    @Transactional
    public void importGenTableForJDBC(List<GenTable> tableList, JDBCProperies jdbcProperies)
    {
        String operName = "";
        for (GenTable table : tableList)
        {
            try
            {
                String tableName = table.getTableName();
                GenUtils.initTable(table, operName);
                table.setJdbcurl(jdbcProperies.getJdbcurl());
                int row = genTableMapper.insertGenTable(table);
                if (row > 0)
                {
                    // 保存列信息
                    List<GenTableColumn> genTableColumns = genTableColumnService.selectDbTableColumnsByName(tableName,jdbcProperies);
                    for (GenTableColumn column : genTableColumns)
                    {
                        GenUtils.initColumnField(column, table);
                        column.setJdbcurl(jdbcProperies.getJdbcurl());
                        if(StringUtils.isEmpty(column.getQueryType())){
                            column.setQueryType("EQ");
                        }
                        genTableColumnMapper.insertGenTableColumn(column);
                    }
                }
            }
            catch (Exception e)
            {
                log.error("表名 " + table.getTableName() + " 导入失败：", e);
            }
        }
    }

    /**
     * 预览代码
     *
     * @param tableId 表编号
     * @return 预览数据列表
     */
    public Map<String, String> previewCode(Long tableId)
    {
        Map<String, String> dataMap = new LinkedHashMap<>();
        // 查询表信息
        GenTable table = genTableMapper.selectGenTableById(tableId);
        // 查询列信息
        List<GenTableColumn> columns = table.getColumns();
        setPkColumn(table, columns);
        VelocityInitializer.initVelocity();

        VelocityContext context = VelocityUtils.prepareContext(table);

        // 获取模板列表
        List<String> templates = VelocityUtils.getTemplateList(table.getTplCategory());
        for (String template : templates)
        {
            // 渲染模板
            StringWriter sw = new StringWriter();
            Template tpl = Velocity.getTemplate(template, "UTF-8");
            tpl.merge(context, sw);
            dataMap.put(template, sw.toString());
        }
        return dataMap;
    }

    /**
     * 生成代码
     * 
     * @param tableName 表名称
     * @return 数据
     */
    @Override
    public byte[] generatorCode(String tableName)
    {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        ZipOutputStream zip = new ZipOutputStream(outputStream);
        generatorCode(tableName, zip);
        IOUtils.closeQuietly(zip);
        return outputStream.toByteArray();
    }

    /**
     * 批量生成代码
     * 
     * @param tableNames 表数组
     * @return 数据
     */
    @Override
    public byte[] generatorCode(String[] tableNames)
    {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        ZipOutputStream zip = new ZipOutputStream(outputStream);
        for (String tableName : tableNames)
        {
            generatorCode(tableName, zip);
        }
        IOUtils.closeQuietly(zip);
        return outputStream.toByteArray();
    }

    /**
     * 查询表信息并生成代码
     */
    private void generatorCode(String tableId, ZipOutputStream zip)
    {
        // 查询表信息
        GenTable table = genTableMapper.selectGenTableByName(tableId);
        // 查询列信息
        List<GenTableColumn> columns = table.getColumns();
        setPkColumn(table, columns);

        VelocityInitializer.initVelocity();

        VelocityContext context = VelocityUtils.prepareContext(table);

        // 获取模板列表
        List<String> templates = VelocityUtils.getTemplateList(table.getTplCategory());
        for (String template : templates)
        {
            // 渲染模板
            StringWriter sw = new StringWriter();
            Template tpl = Velocity.getTemplate(template, "UTF-8");
            tpl.merge(context, sw);
            try
            {
                // 添加到zip
                zip.putNextEntry(new ZipEntry(VelocityUtils.getFileName(template, table)));
                IOUtils.write(sw.toString(), zip, "UTF-8");
                IOUtils.closeQuietly(sw);
                zip.closeEntry();
            }
            catch (IOException e)
            {
                log.error("渲染模板失败，表名：" + table.getTableName(), e);
            }
        }
    }

    /**
     * 修改保存参数校验
     * 
     * @param genTable 业务信息
     */
    public void validateEdit(GenTable genTable)
    {
//        if (GenConstants.TPL_TREE.equals(genTable.getTplCategory()))
//        {
//            String options = JSON.toJSONString(genTable.getParams());
//            JSONObject paramsObj = JSONObject.parseObject(options);
//            if (StringUtils.isEmpty(paramsObj.getString(GenConstants.TREE_CODE)))
//            {
//                throw new BusinessException("树编码字段不能为空");
//            }
//            else if (StringUtils.isEmpty(paramsObj.getString(GenConstants.TREE_PARENT_CODE)))
//            {
//                throw new BusinessException("树父编码字段不能为空");
//            }
//            else if (StringUtils.isEmpty(paramsObj.getString(GenConstants.TREE_NAME)))
//            {
//                throw new BusinessException("树名称字段不能为空");
//            }
//        }
    }

    /**
     * 设置主键列信息
     * 
     * @param
     * @param columns 业务字段列表
     */
    public void setPkColumn(GenTable table, List<GenTableColumn> columns)
    {
        for (GenTableColumn column : columns)
        {
            if (column.isPk())
            {
                table.setPkColumn(column);
                break;
            }
        }
        if (StringUtils.isNull(table.getPkColumn()))
        {
            table.setPkColumn(columns.get(0));
        }
    }

    /**
     * 设置代码生成其他选项值
     * 
     * @param genTable 设置后的生成对象
     */
    public void setTableFromOptions(GenTable genTable)
    {
        JSONObject paramsObj = JSONObject.parseObject(genTable.getOptions());
        if (StringUtils.isNotNull(paramsObj))
        {
            String treeCode = paramsObj.getString(GenConstants.TREE_CODE);
            String treeParentCode = paramsObj.getString(GenConstants.TREE_PARENT_CODE);
            String treeName = paramsObj.getString(GenConstants.TREE_NAME);
            genTable.setTreeCode(treeCode);
            genTable.setTreeParentCode(treeParentCode);
            genTable.setTreeName(treeName);
        }
    }

}